Alter  proc sp_create_update_fgi_lot (


@PART_NUMBER VARCHAR(50), 	--------partNumber
@LOT_NUMBER VARCHAR(50), 	--------lotNumber
@QTY_IN INT,			--------QtyIn
@DATE_IN DATETIME,		--------DateIn
@VENDOR_NAME VARCHAR(50),	--------VendorName
@LOT_COMMENTS VARCHAR(1000),    --------Comments of The Lot
@STATUS VARCHAR(20),		--------Status
@SHIPPEDLOT_ID INT,		--------ShippedLot
@SHIP_REQ_ID   INT,
@TEMP_LOT_ID   BIGINT
)

AS
BEGIN

	DECLARE @PROCESS_STEP_ID INT
  	DECLARE @LOCATION_ID INT
  	DECLARE @INT INT
  	DECLARE @NORMAL_COST FLOAT
  	DECLARE @LOT_ID INT
  	DECLARE @THIS_LOT_STATUS VARCHAR(50)
	DECLARE @ASSM_RELEASE_NO VARCHAR(50)
	DECLARE @temp_ship_req_id INT
	DECLARE @ship_request_close_flag INT
	

	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE 'CLASS STORE' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
  	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'CLASS STORE' AND L_VENDOR_NAME = @VENDOR_NAME
  
  	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
  	BEGIN
  		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE 'CLASS STORE' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
  		IF @PROCESS_STEP_ID IS NULL
  		BEGIN
  			EXEC SP_CREATE_UPDATE_MISCELLANEOUS_LOT @PART_NUMBER, @LOT_NUMBER, NULL,NULL, NULL, NULL,
  			@VENDOR_NAME, 'BLANK FGI', 'CLASS STORE', NULL, @DATE_IN, NULL, NULL, NULL, NULL, NULL, @QTY_IN, NULL, NULL, NULL, 'RUNNING'
  			
			UPDATE SHIPPED_LOT SET SHIP_STATUS = 1, STATUS = 'CLOSE' WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
  			-- dummy update to avoid SQLException
  			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
  			RETURN
  		END
  	END
  
  	IF @PROCESS_STEP_ID IS NOT NULL
  	BEGIN
  		UPDATE MISCELLANEOUS_LOT_TABLE SET STATUS = 'CLOSE' WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PS_TYPE_NAME LIKE 'CLASS STORE'
  	END
  
  	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
  
  	EXEC SP_CLOSE_TRANSIT_LOTS_BY_SHIPPNG_LOT_ID @PROCESS_STEP_ID, @LOT_NUMBER, @LOCATION_ID, @QTY_IN, @DATE_IN
	
	/* To close the lot, if lot in running state in previous location */
	EXEC SP_CLOSE_LOTS_BY_PREVIOUS_PROCESS_STEP_ID @PROCESS_STEP_ID, @LOT_NUMBER, @LOCATION_ID, 'CLASS STORE'
  	
  	IF @LOT_ID IS NULL -- Check if the lot exists
  	BEGIN -- Create Lot
  		SELECT @NORMAL_COST = PSLT_COST_NORMAL FROM PROCESS_STEP_LOCATION_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID
 
 		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_COST, LOT_CURRENT_STATUS, TIMESTAMP, LOT_COMMENTS, LOT_ENTERED_BY ) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER,  (@NORMAL_COST * @QTY_IN), 'RUNNING', CURRENT_TIMESTAMP, @LOT_COMMENTS, 0)
  		SELECT @LOT_ID = @@IDENTITY				
  	END	
  	ELSE 
  	BEGIN
  		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, LOT_COMMENTS = @LOT_COMMENTS , LOT_ENTERED_BY = 0 WHERE LOT_ID = @LOT_ID 
  	END
	
	/* For Closing the Assembly Release Lots */
 	DECLARE @ASSEMBLYRELEASECURSOR CURSOR
 	SET @ASSEMBLYRELEASECURSOR = CURSOR FOR SELECT DISTINCT AR.ASSEMBLY_RELEASE_NO FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID AND STATUS = 'APPROVED'
 	OPEN @ASSEMBLYRELEASECURSOR
 	FETCH NEXT FROM @ASSEMBLYRELEASECURSOR INTO @ASSM_RELEASE_NO
 				
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 		IF @ASSM_RELEASE_NO = @LOT_NUMBER
 		BEGIN
 			UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_NO = @ASSM_RELEASE_NO
 		END
 			
 	FETCH NEXT FROM @ASSEMBLYRELEASECURSOR INTO @ASSM_RELEASE_NO
 	END
 	CLOSE @ASSEMBLYRELEASECURSOR
 	DEALLOCATE @ASSEMBLYRELEASECURSOR
 	
 	/* END OF CLOSING ASSEMBLY RELESE LOTS */
	
	IF @SHIP_REQ_ID <> -1 AND @TEMP_LOT_ID <> -1
	BEGIN
		UPDATE Shipping_Request_Lot_table set Ship_Req_Lot_Status = 'Closed' WHERE Ship_Req_Id = @ship_req_id AND LOT_ID = @temp_lot_id
		
		DECLARE @shipReqCursor CURSOR
		SET @shipReqCursor = CURSOR FOR SELECT SRL.Ship_Req_Id FROM Shipping_Request_table SR, Shipping_Request_Lot_Table SRL WHERE SRL.Ship_Req_Id = SR.Ship_Req_Id and SRL.Ship_Req_Id = @ship_req_id and STATUS = 'Approved' 
		OPEN @shipReqCursor
		FETCH NEXT FROM @shipReqCursor INTO @temp_ship_req_id

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @ship_request_close_flag = 0 	--  0 means Need to Close Ship Request.
			
			IF ( ( SELECT COUNT(*) FROM Shipping_Request_Lot_Table WHERE Ship_Req_Id = @ship_req_id AND Ship_Req_Lot_Status = 'Approved' ) = 0 )
			BEGIN
				SET @ship_request_close_flag = 1				
			END
			
			--Close the Ship Request, as all of its lots shipped.
			IF ( @ship_request_close_flag = 1 )
			BEGIN
				UPDATE Shipping_Request_table SET STATUS = 'Closed' WHERE Ship_Req_Id = @ship_req_id
			END
			SET @ship_req_id = null
			
			FETCH NEXT FROM @shipReqCursor INTO @temp_ship_req_id
		END
		CLOSE @shipReqCursor
		DEALLOCATE @shipReqCursor
	END

	-- insert/update Inventory Lot
  	DECLARE @INV_LOT_ID INT
	SELECT @INV_LOT_ID = LOT_ID  FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
	IF @INV_LOT_ID IS NULL
  	BEGIN
  		INSERT INTO INVENTORY_LOT ( LOT_ID, IL_QTY_IN, IL_QTY_OUT, IL_DATE_IN, IL_DATE_OUT, TIMESTAMP ) VALUES (@LOT_ID, @QTY_IN, NULL, @DATE_IN, NULL, CURRENT_TIMESTAMP )
		--to close shipped_lot
		UPDATE SHIPPED_LOT SET SHIP_STATUS = 1, STATUS = 'Close' WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
 	END
	ELSE
	BEGIN
		UPDATE INVENTORY_LOT SET IL_DATE_IN = @DATE_IN, IL_QTY_IN = @QTY_IN, IL_QTY_OUT = NULL, IL_DATE_OUT = NULL, TIMESTAMP = CURRENT_TIMESTAMP  WHERE LOT_ID = @INV_LOT_ID
		--to close shipped_lot
		UPDATE SHIPPED_LOT SET SHIP_STATUS = 1, STATUS = 'Close' WHERE SHIPPEDLOT_ID = @SHIPPEDLOT_ID
	END

END